﻿Imports System.Windows.Forms
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Microsoft.Win32

Public Class Customer_Details
    Dim SQLResult As Object
    Dim Records_Retrieved As String = 0
    Dim conn As New SqlConnection(csb.ToString())
    Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
        Me.DialogResult = System.Windows.Forms.DialogResult.OK
        Try
            cmd.Connection = conn
            conn.Open()
            'Check if Duplicate - need to use dr. loop

            cmd.CommandText = "INSERT INTO CUST_TB ( [Name], [Street1], [Street2], [City], [State], [Country], [ZipCode], [Notes] ) VALUES ('" + TB_Name.Text + "', '" + TB_Add1.Text + "', '" + TB_Add2.Text + "', '" + TB_City.Text + "', '" + TB_State.Text + "', '" + TB_Country.Text + "', '" + TB_Zip.Text + "', '" + TB_Notes.Text + "');"
            cmd.ExecuteScalar()
            MsgBox(TB_Name.Text + " has been added to the Customer list.", MsgBoxStyle.Information)
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.message + vbCrLf + vbCrLf + "Connection Failed")
            conn.Close()
            Me.Cursor = Cursors.Default
        End Try
Finish:
        Me.Close()
    End Sub

    Private Sub Cancel_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel_Button.Click
        TB_Name.ForeColor = Color.White
        TB_Name.Text = "Cancel"
        Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
        Me.Close()
    End Sub


    Private Sub TB_Name_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TB_Name.Leave
        If Cancel_Button.ContainsFocus Then
            'Do Nothing
        Else
            If TB_Name.Text = "" Then
                MsgBox("Customer name cannot be blank.", MsgBoxStyle.Information)
                TB_Name.Select()
            Else
                If ESBD_Form.ListItem_Exists("CUST_TB", "Name", TB_Name.Text) = True Then
                    MsgBox(TB_Name.Text + " already exists in the database." + vbCrLf + vbCrLf + "Please enter a different name and try again.", MsgBoxStyle.Exclamation Or MsgBoxStyle.OkOnly)
                    TB_Name.Select()
                Else
                    'Continue
                End If
            End If
        End If
Finish:
    End Sub

    Private Sub Customer_Details_Close(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.FormClosed
        BTN_Save.Visible = False
        BTN_Cancel2.Visible = False
        LBL_Name.Text = "Name"
        cmb_CustName.Visible = False
        GroupBox2.Text = "Name"
        TLP_OkCancel.Visible = True
        TB_Name.Visible = True
    End Sub

    Private Sub cmb_CustName_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_CustName.Click
        cmb_CustName.DroppedDown = True
    End Sub

    Private Sub cmb_CustName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_CustName.SelectedIndexChanged
        Dim curName As String = cmb_CustName.Text
        TB_Add1.Text = ""
        TB_Add2.Text = ""
        TB_City.Text = ""
        TB_Country.Text = ""
        TB_Notes.Text = ""
        TB_State.Text = ""
        TB_Zip.Text = ""
        
        'Load Customer Record
        Try
            cmd.Connection = conn
            conn.Open()
            'Find address1
            cmd.CommandText = "Select Street1 from CUST_TB where ""Name"" = '" + curName + "'"
            SQLResult = cmd.ExecuteScalar
            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                TB_Add1.Text = SQLResult
            End If

            'Find address2
            cmd.CommandText = "Select Street2 from CUST_TB where ""Name"" = '" + curName + "'"
            SQLResult = cmd.ExecuteScalar
            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                TB_Add2.Text = SQLResult
            End If

            'Find city
            cmd.CommandText = "Select City from CUST_TB where ""Name"" = '" + curName + "'"
            SQLResult = cmd.ExecuteScalar
            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                TB_City.Text = SQLResult
            End If

            'Find state
            cmd.CommandText = "Select State from CUST_TB where ""Name"" = '" + curName + "'"
            SQLResult = cmd.ExecuteScalar
            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                TB_State.Text = SQLResult
            End If

            'Find country
            cmd.CommandText = "Select Country from CUST_TB where ""Name"" = '" + curName + "'"
            SQLResult = cmd.ExecuteScalar
            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                TB_Country.Text = SQLResult
            End If

            'Find zip
            cmd.CommandText = "Select ZipCode from CUST_TB where ""Name"" = '" + curName + "'"
            SQLResult = cmd.ExecuteScalar
            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                TB_Zip.Text = SQLResult
            End If

            'Find notes
            cmd.CommandText = "Select Notes from CUST_TB where ""Name"" = '" + curName + "'"
            SQLResult = cmd.ExecuteScalar
            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                TB_Notes.Text = SQLResult
            End If

            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message + vbCrLf + vbCrLf + "Connection Failed")
            conn.Close()
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub BTN_Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTN_Save.Click
        Try
            cmd.Connection = conn
            conn.Open()

            cmd.CommandText = "Update CUST_TB set Street1 = '" + TB_Add1.Text + "', Street2 = '" + TB_Add2.Text + "', City = '" + TB_City.Text + "', State = '" + TB_State.Text + "', Country = '" + TB_Country.Text + "', ZipCode = '" + TB_Zip.Text + "', Notes = '" + TB_Notes.Text + "' where Name = '" + cmb_CustName.Text + "'"
            cmd.ExecuteScalar()
            MsgBox(cmb_CustName.Text + " has been successfully edited.", MsgBoxStyle.Information)
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message + vbCrLf + vbCrLf + "Connection Failed")
            conn.Close()
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub BTN_Cancel2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTN_Cancel2.Click
        Me.Close()
    End Sub
End Class
